{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入库和数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sys\n",
    "import seaborn as sns\n",
    "from time import sleep\n",
    "import matplotlib.pyplot as plt\n",
    "on_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\ccf_online_stage1_train\\ccf_online_stage1_train.csv')\n",
    "off_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\ccf_offline_stage1_train\\ccf_offline_stage1_train.csv')\n",
    "# oftid_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\off_train_sameUser_id_test.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\TCForNewComer\\ccf_offline_stage1_test_revised.csv')\n",
    "samplt=pd.read_csv(r'D:\\Data\\TCForNewComer\\sample_submission.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1433</td>\n",
       "      <td>8735</td>\n",
       "      <td>30:5</td>\n",
       "      <td>10</td>\n",
       "      <td>20160214</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>1469</td>\n",
       "      <td>2902</td>\n",
       "      <td>0.95</td>\n",
       "      <td>10</td>\n",
       "      <td>20160607</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35</td>\n",
       "      <td>3381</td>\n",
       "      <td>1807</td>\n",
       "      <td>300:30</td>\n",
       "      <td>0</td>\n",
       "      <td>20160130</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35</td>\n",
       "      <td>3381</td>\n",
       "      <td>9776</td>\n",
       "      <td>10:5</td>\n",
       "      <td>0</td>\n",
       "      <td>20160129</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35</td>\n",
       "      <td>3381</td>\n",
       "      <td>11951</td>\n",
       "      <td>200:20</td>\n",
       "      <td>0</td>\n",
       "      <td>20160129</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  Date\n",
       "0        4         1433      8735          30:5       10      20160214  null\n",
       "1        4         1469      2902          0.95       10      20160607  null\n",
       "2       35         3381      1807        300:30        0      20160130  null\n",
       "3       35         3381      9776          10:5        0      20160129  null\n",
       "4       35         3381     11951        200:20        0      20160129  null"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "off_train=off_train.sort_values(by=['User_id'])\n",
    "off_train.index=np.arange(0,len(off_train),1)\n",
    "off_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4129537</td>\n",
       "      <td>450</td>\n",
       "      <td>9983</td>\n",
       "      <td>30:5</td>\n",
       "      <td>1</td>\n",
       "      <td>20160712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6949378</td>\n",
       "      <td>1300</td>\n",
       "      <td>3429</td>\n",
       "      <td>30:5</td>\n",
       "      <td>null</td>\n",
       "      <td>20160706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2166529</td>\n",
       "      <td>7113</td>\n",
       "      <td>6928</td>\n",
       "      <td>200:20</td>\n",
       "      <td>5</td>\n",
       "      <td>20160727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2166529</td>\n",
       "      <td>7113</td>\n",
       "      <td>1808</td>\n",
       "      <td>100:10</td>\n",
       "      <td>5</td>\n",
       "      <td>20160727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6172162</td>\n",
       "      <td>7605</td>\n",
       "      <td>6500</td>\n",
       "      <td>30:1</td>\n",
       "      <td>2</td>\n",
       "      <td>20160708</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  Coupon_id Discount_rate Distance Date_received\n",
       "0  4129537          450       9983          30:5        1      20160712\n",
       "1  6949378         1300       3429          30:5     null      20160706\n",
       "2  2166529         7113       6928        200:20        5      20160727\n",
       "3  2166529         7113       1808        100:10        5      20160727\n",
       "4  6172162         7605       6500          30:1        2      20160708"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['Date_received']=test['Date_received'].astype(np.int64)\n",
    "test['Date_received']=test['Date_received'].apply(lambda x: str(x))\n",
    "test.index=np.arange(0,len(test),1)\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Action</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>25104</td>\n",
       "      <td>2</td>\n",
       "      <td>100145044</td>\n",
       "      <td>100:10</td>\n",
       "      <td>20160331</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>45612</td>\n",
       "      <td>1</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>36</td>\n",
       "      <td>46701</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>64</td>\n",
       "      <td>11200</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>64</td>\n",
       "      <td>29214</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160606</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  Action  Coupon_id Discount_rate Date_received  \\\n",
       "0        4        25104       2  100145044        100:10      20160331   \n",
       "1        4        45612       1       null          null          null   \n",
       "2       36        46701       0       null          null          null   \n",
       "3       64        11200       0       null          null          null   \n",
       "4       64        29214       0       null          null          null   \n",
       "\n",
       "       Date  \n",
       "0      null  \n",
       "1  20160308  \n",
       "2  20160120  \n",
       "3  20160526  \n",
       "4  20160606  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "on_train=on_train.sort_values(by=['User_id'])\n",
    "on_train.index=np.arange(0,len(on_train),1)\n",
    "on_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "col='Merchant_id'\n",
    "a=set(on_train[col].values)\n",
    "b=set(test[col].values)\n",
    "c=set(oftid_train[col].values)\n",
    "print(len(b))\n",
    "print(len(a))\n",
    "print(len(c))\n",
    "\n",
    "print(len(a&b))\n",
    "print(len(b&c))\n",
    "print(len(c-b))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 只保留和test的User_id相同的行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "b=set(test['User_id'].values)\n",
    "c=set(off_train['User_id'].values)\n",
    "print(len(b))\n",
    "print(len(c))\n",
    "print(len(b&c))\n",
    "\n",
    "#保留off_train中的和test的User_id一样的行\n",
    "row=[]\n",
    "i=0\n",
    "j=10\n",
    "length=len(c&b)\n",
    "for inde in (c&b):\n",
    "    rate=int(i*100/length)\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%')\n",
    "        j=j+10\n",
    "    row.extend(list(off_train[off_train['User_id']==inde].index))\n",
    "    i+=1\n",
    "off_train=off_train.loc[row]\n",
    "\n",
    "off_train.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\off_train_sameUser_id_test.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "a=set(on_train['User_id'].values)\n",
    "b=set(test['User_id'].values)\n",
    "print(len(b))\n",
    "print(len(a))\n",
    "print(len(a&b))\n",
    "\n",
    "#保留on_train中的和test的User_id一样的行\n",
    "row=[]\n",
    "i=0\n",
    "j=10\n",
    "length=len(a&b)\n",
    "for inde in (a&b):\n",
    "    rate=int(i*100/length)\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%')\n",
    "        j=j+10\n",
    "    row.extend(list(on_train[on_train['User_id']==inde].index))\n",
    "    i+=1\n",
    "on_train=on_train.loc[row]\n",
    "\n",
    "on_train.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\on_train_sameUser_id_test.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入处理过以后的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "oftid_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\off_train_sameUser_id_test.csv')\n",
    "ontid_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\on_train_sameUser_id_test.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 增加一列之前没用优惠券是否购买过相同商品和次数\n",
    "- 可能是日常用品，或者零食\n",
    "- 可能是耐用品，然后买了之后就不会再买"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "indexs=oftid_train[(oftid_train['Coupon_id']=='null')&(oftid_train['Date']!='null')].index\n",
    "odinary=oftid_train.iloc[indexs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "oftid_train.drop(indexs,axis=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "oftid_train['ord_buy']=0#增加一列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "odinary.index=np.arange(0,len(odinary),1)\n",
    "oftid_train.index=np.arange(0,len(oftid_train),1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#计算同一件商品半年内普通购买次数\n",
    "from ipykernel import kernelapp as app\n",
    "i=0\n",
    "j=10\n",
    "length=len(oftid_train)\n",
    "for index in oftid_train.index:\n",
    "    rate=int(i*100/length)#看进度\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%')\n",
    "        j=j+10\n",
    "\n",
    "    uid=oftid_train.iloc[index]['User_id']\n",
    "    mid=oftid_train.iloc[index]['Merchant_id']\n",
    "    if len(odinary[odinary['User_id']==uid].index)!=0:\n",
    "        for ind in odinary[odinary['User_id']==uid].index:\n",
    "            if odinary.iloc[ind]['Merchant_id']==mid:\n",
    "                oftid_train.loc[index,'ord_buy']+=1\n",
    "    i+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "oftid_train.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\oftid_addordinarybuy_train.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test['ord_buy']=0#增加一列\n",
    "test.index=np.arange(0,len(test),1)\n",
    "\n",
    "#计算同一件商品半年内普通购买次数\n",
    "i=0\n",
    "j=10\n",
    "length=len(test)\n",
    "for index in test.index:\n",
    "    rate=int(i*100/length)#看进度\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%')\n",
    "        j=j+10\n",
    "\n",
    "    uid=test.iloc[index]['User_id']\n",
    "    mid=test.iloc[index]['Merchant_id']\n",
    "    if len(odinary[odinary['User_id']==uid].index)!=0:\n",
    "        for ind in odinary[odinary['User_id']==uid].index:\n",
    "            if odinary.iloc[ind]['Merchant_id']==mid:\n",
    "                test.loc[index,'ord_buy']+=1\n",
    "    i+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\test_addordinarybuy.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 线下领取优惠券张数和不同优惠券的张数和使用数目"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "or_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\oftid_addordinarybuy_train.csv')\n",
    "or_test=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\test_addordinarybuy.csv')\n",
    "off_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\ccf_offline_stage1_train\\ccf_offline_stage1_train.csv')\n",
    "or_train=or_train.sort_values(by=['User_id'])\n",
    "off_train.index=np.arange(0,len(off_train),1)\n",
    "or_train.index=np.arange(0,len(or_train),1)\n",
    "or_test.index=np.arange(0,len(or_test),1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def Discount_rate(x):#转化函数\n",
    "    if x.startswith('0'):\n",
    "        return float(x)\n",
    "    else:\n",
    "        return int(x.split(':')[0])-int(x.split(':')[1])\n",
    "    #将满减转化为小数\n",
    "or_train['Discount_rate']=or_train['Discount_rate'].apply(Discount_rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 216250 entries, 0 to 216249\n",
      "Data columns (total 8 columns):\n",
      "User_id          216250 non-null int64\n",
      "Merchant_id      216250 non-null int64\n",
      "Coupon_id        216250 non-null int64\n",
      "Discount_rate    216250 non-null float64\n",
      "Distance         216250 non-null object\n",
      "Date_received    216250 non-null int64\n",
      "Date             216250 non-null object\n",
      "ord_buy          216250 non-null int64\n",
      "dtypes: float64(1), int64(5), object(2)\n",
      "memory usage: 14.8+ MB\n"
     ]
    }
   ],
   "source": [
    "or_train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ". 10 %-.. 20 %-... 30 %-.... 40 %-..... 50 %-...... 60 %-....... 70 %-........ 80 %-......... 90 %-"
     ]
    }
   ],
   "source": [
    "or_train['Coupon_num']=0                   #领取张数\n",
    "or_train['Coupon_nouse_num']=0             #领取没有使用张数\n",
    "or_train['Coupon_use_num']=0               #使用张数\n",
    "or_train['Coupon_use_prob']=0              #使用概率\n",
    "i=0\n",
    "j=10\n",
    "length=len(set(or_train['User_id'].values))\n",
    "for id in set(or_train['User_id'].values):\n",
    "    rate=int(i*100/length)#看进度\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%',end='-')\n",
    "        j=j+10\n",
    "    \n",
    "    mid_Ser=or_train[or_train['User_id']==id]['Date']\n",
    "    lengths=len(mid_Ser[mid_Ser=='null'])\n",
    "    or_train.loc[mid_Ser.index,'Coupon_num']=len(mid_Ser)\n",
    "    or_train.loc[mid_Ser.index,'Coupon_nouse_num']=lengths\n",
    "    or_train.loc[mid_Ser.index,'Coupon_use_num']=len(mid_Ser)-lengths\n",
    "    or_train.loc[mid_Ser.index,'Coupon_use_prob']=float(len(mid_Ser)-lengths)/len(mid_Ser)\n",
    "    i+=1\n",
    "or_train.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\or_train.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ". 10 %-.. 20 %-... 30 %-.... 40 %-..... 50 %-...... 60 %-....... 70 %-........ 80 %-......... 90 %-"
     ]
    }
   ],
   "source": [
    "or_test['Coupon_num']=0                   #领取张数\n",
    "or_test['Coupon_nouse_num']=0             #领取没有使用张数\n",
    "or_test['Coupon_use_num']=0               #使用张数\n",
    "or_test['Coupon_use_prob']=0              #使用概率\n",
    "i=0\n",
    "j=10\n",
    "length=len(set(or_test['User_id'].values))\n",
    "for id in set(or_test['User_id'].values):\n",
    "    rate=int(i*100/length)#看进度\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%',end='-')\n",
    "        j=j+10\n",
    "    \n",
    "    mid_Ser=or_train[or_train['User_id']==id]['Date']\n",
    "    if len(mid_Ser)!=0:\n",
    "        lengths=len(mid_Ser[mid_Ser=='null'])\n",
    "        test_Ser=or_test[or_test['User_id']==id]\n",
    "        or_test.loc[test_Ser.index,'Coupon_num']=len(mid_Ser)\n",
    "        or_test.loc[test_Ser.index,'Coupon_nouse_num']=lengths\n",
    "        or_test.loc[test_Ser.index,'Coupon_use_num']=len(mid_Ser)-lengths\n",
    "        or_test.loc[test_Ser.index,'Coupon_use_prob']=float(len(mid_Ser)-lengths)/len(mid_Ser)\n",
    "    i+=1\n",
    "or_test.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\or_test.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 使用优惠券面额大于100的次数和小于100的次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "oc_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\train.csv')\n",
    "oc_test=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\test.csv')\n",
    "oc_train=oc_train.sort_values(by=['User_id'])\n",
    "oc_train.index=np.arange(0,len(oc_train),1)\n",
    "oc_test.index=np.arange(0,len(oc_test),1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 353,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ". 10 %-.. 20 %-... 30 %-.... 40 %-..... 50 %-...... 60 %-....... 70 %-........ 80 %-......... 90 %-"
     ]
    }
   ],
   "source": [
    "oc_train['Coupon_greaterthan100']=0                   #使用面额大于100的张数\n",
    "oc_train['Coupon_lessthan100']=0                      #使用面额小于100的张数\n",
    "oc_train['Coupon_lep100']=0                          #使用面额大于100的张数占使用张数的比重\n",
    "oc_train['Coupon_grp100']=0                          #使用面额小于100的张数占使用张数的比重\n",
    "oc_train['Avg_saler_Coupon']=0                       #核销每个商家多少张优惠券\n",
    "oc_train['Avg_distance']=0                          #用户核销优惠券的平均距离\n",
    "oc_train['shop_count']=0                            #核销商家数量\n",
    "oc_train['shop_pro']=0                              #商家优惠券占总的比例\n",
    "oc_train['Coupon_diffcount']=0                      #不同优惠券的数量\n",
    "oc_train['Coupon_diffpro']=0                        #不同优惠券的占比\n",
    "oc_train['same_Coupon_count']=0                      #相同优惠券领取的次数\n",
    "i=0\n",
    "j=10\n",
    "length=len(set(oc_train[oc_train['Date']==1]['User_id'].values))\n",
    "for id in set(oc_train[oc_train['Date']==1]['User_id'].values):\n",
    "    rate=int(i*100/length)#看进度\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%',end='-')\n",
    "        j=j+10\n",
    "    \n",
    "    discount_rate=oc_train[(oc_train['User_id']==id)&(oc_train['Date']==1)]['Discount_rate']\n",
    "    indexs=oc_train[oc_train['User_id']==id].index\n",
    "    gr100=len(discount_rate[discount_rate>100])\n",
    "    le100=len(discount_rate[discount_rate<100])\n",
    "    use_count=oc_train.loc[discount_rate.index,'Coupon_use_num'].max()\n",
    "    oc_train.loc[indexs,'Coupon_greaterthan100']=gr100\n",
    "    oc_train.loc[indexs,'Coupon_lessthan100']=le100\n",
    "    oc_train.loc[indexs,'Coupon_grp100']=gr100/use_count\n",
    "    oc_train.loc[indexs,'Coupon_lep100']=le100/use_count\n",
    "    \n",
    "    customer=oc_train[(oc_train['User_id']==id)&(oc_train['Date']==1)]\n",
    "    oc_train.loc[indexs,'shop_count']=len(set(customer['Distance'].values))\n",
    "    oc_train.loc[indexs,'Coupon_diffcount']=len(set(customer['Discount_rate'].values))\n",
    "    for dis in set(customer['Distance'].values):                     #不同的商家\n",
    "        cus=customer[customer['Distance']==dis]\n",
    "        indexss=oc_train[(oc_train['User_id']==id)&(oc_train['Distance']==dis)].index\n",
    "        oc_train.loc[indexss,'Avg_saler_Coupon']=len(cus)\n",
    "        oc_train.loc[indexss,'shop_pro']=len(cus)/len(customer)\n",
    "        for rat in set(customer['Discount_rate'].values):             #不同优惠额度\n",
    "            Rate=customer[customer['Discount_rate']==rat]\n",
    "            indexsss=oc_train[(oc_train['User_id']==id)&(oc_train['Distance']==dis)&(oc_train['Discount_rate']==rat)].index\n",
    "            oc_train.loc[indexsss,'Coupon_diffpro']=float(len(Rate))/len(cus)\n",
    "    \n",
    "    for coupid in set(oc_train[oc_train['User_id']==id]['Coupon_id'].values):\n",
    "        indexx=oc_train[(oc_train['User_id']==id)&(oc_train['Coupon_id']==coupid)].index\n",
    "        oc_train.loc[indexx,'same_Coupon_count']=len(indexx)\n",
    "        \n",
    "    oc_train.loc[indexs,'Avg_distance']=customer['Distance'].mean()\n",
    "    i+=1\n",
    "oc_train.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\oc_train.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ". 10 %-.. 20 %-... 30 %-.... 40 %-..... 50 %-...... 60 %-....... 70 %-........ 80 %-......... 90 %-"
     ]
    }
   ],
   "source": [
    "oc_test['Coupon_greaterthan100']=0                   #使用面额大于100的张数\n",
    "oc_test['Coupon_lessthan100']=0                      #使用面额小于100的张数\n",
    "oc_test['Coupon_lep100']=0                          #使用面额大于100的张数占使用张数的比重\n",
    "oc_test['Coupon_grp100']=0                          #使用面额小于100的张数占使用张数的比重\n",
    "oc_test['Avg_saler_Coupon']=0                       #核销每个商家多少张优惠券\n",
    "oc_test['Avg_distance']=0                          #用户核销优惠券的平均距离\n",
    "oc_test['shop_count']=0                            #核销商家数量\n",
    "oc_test['shop_pro']=0                              #商家优惠券占总的比例\n",
    "oc_test['Coupon_diffcount']=0                      #不同优惠券的数量\n",
    "oc_test['Coupon_diffpro']=0                        #不同优惠券的占比\n",
    "oc_test['same_Coupon_count']=0                      #相同优惠券领取的次数\n",
    "i=0\n",
    "j=10\n",
    "length=len(set(oc_test['User_id'].values))\n",
    "for id in set(oc_test['User_id'].values):\n",
    "    rate=int(i*100/length)#看进度\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%',end='-')\n",
    "        j=j+10\n",
    "    \n",
    "    discount_rate=oc_train[(oc_train['User_id']==id)&(oc_train['Date']==1)]['Discount_rate']\n",
    "    indexs=oc_test[oc_test['User_id']==id].index\n",
    "    gr100=len(discount_rate[discount_rate>100])\n",
    "    le100=len(discount_rate[discount_rate<100])\n",
    "    use_count=oc_train.loc[discount_rate.index,'Coupon_use_num'].max()\n",
    "    oc_test.loc[indexs,'Coupon_greaterthan100']=gr100\n",
    "    oc_test.loc[indexs,'Coupon_lessthan100']=le100\n",
    "    if len(discount_rate)!=0:\n",
    "        oc_test.loc[indexs,'Coupon_grp100']=gr100/use_count\n",
    "        oc_test.loc[indexs,'Coupon_lep100']=le100/use_count\n",
    "    \n",
    "    customer=oc_train[(oc_train['User_id']==id)&(oc_train['Date']==1)]\n",
    "    oc_test.loc[indexs,'shop_count']=len(set(customer['Distance'].values))\n",
    "    oc_test.loc[indexs,'Coupon_diffcount']=len(set(customer['Discount_rate'].values))\n",
    "    for dis in set(oc_test[(oc_test['User_id']==id)]['Distance'].values):                     #不同的商家\n",
    "        cus=customer[customer['Distance']==dis]\n",
    "        indexss=oc_test[(oc_test['User_id']==id)&(oc_test['Distance']==dis)].index\n",
    "        oc_test.loc[indexss,'Avg_saler_Coupon']=len(cus)\n",
    "        if len(customer)!=0:\n",
    "            oc_test.loc[indexss,'shop_pro']=float(len(cus))/len(customer)\n",
    "        \n",
    "        for rat in set(oc_test[(oc_test['User_id']==id)]['Discount_rate'].values):             #不同优惠额度\n",
    "            Rate=customer[customer['Discount_rate']==rat]\n",
    "            indexsss=oc_test[(oc_test['User_id']==id)&(oc_test['Distance']==dis)&(oc_test['Discount_rate']==rat)].index\n",
    "            if len(cus)!=0:\n",
    "                oc_test.loc[indexsss,'Coupon_diffpro']=float(len(Rate))/len(cus)\n",
    "    \n",
    "    for coupid in set(oc_test[oc_test['User_id']==id]['Coupon_id'].values):\n",
    "        indexx=oc_test[(oc_test['User_id']==id)&(oc_test['Coupon_id']==coupid)].index\n",
    "        oc_test.loc[indexx,'same_Coupon_count']=len(indexx)\n",
    "    if len(customer['Distance'])!=0:\n",
    "        oc_test.loc[indexs,'Avg_distance']=customer['Distance'].mean()\n",
    "    i+=1\n",
    "oc_test.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\oc_test.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "hh['Date_received']=hh['Date_received'].apply(lambda x: int(str(x)[1:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for index in kk.index:\n",
    "    kk.loc[index,'Date_received']=int(str(kk.loc[index,'Date_received'])[1:])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  商家提供的优惠券种类和张数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 算有几家店铺，店铺中的商品种类，店铺的优惠券种类和张数，顾客经常使用优惠券的商店，对店铺进行编码，优惠券领取的张数，优惠券使用的张数和占比，发放优惠券的平均面额"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "os_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\oc_train.csv')\n",
    "os_test=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\oc_test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "indx=os_train[os_train['Discount_rate']<1].index\n",
    "discount=os_train.loc[indx]\n",
    "os_train=os_train.drop(indx,axis=0)\n",
    "discount.index=np.arange(0,len(discount),1)\n",
    "os_train.index=np.arange(0,len(os_train),1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def shopn(id):                        #计算店铺数量\n",
    "    for mid in  id:\n",
    "        indexs=[]\n",
    "        indexxx.extend(list(os_train[os_train['Merchant_id']==mid].index))\n",
    "        indexs.extend(list(os_train[os_train['Merchant_id']==mid].index))\n",
    "#         avg=os_train[os_train['Merchant_id']==mid]['Discount_rate'].mean()\n",
    "\n",
    "        for uid in set(os_train[os_train['Merchant_id']==mid]['User_id'].values):\n",
    "            dis=os_train[(os_train['Merchant_id']==mid)&(os_train['User_id']==uid)]['Distance'].max()\n",
    "            time_min=os_train[(os_train['Merchant_id']==mid)&(os_train['User_id']==uid)]['Date_received'].min()\n",
    "            time_max=os_train[(os_train['Merchant_id']==mid)&(os_train['User_id']==uid)]['Date_received'].max()\n",
    "            merchantid=set()\n",
    "            discount_rate=os_train[(os_train['Merchant_id']==mid)&(os_train['User_id']==uid)]['Discount_rate'].max()\n",
    "            for ind in os_train[(os_train['User_id']==uid)&(os_train['Distance']==int(dis))].index:\n",
    "                if (os_train.loc[ind,'Discount_rate']==discount_rate) or ((abs(os_train.loc[ind,'Date_received']-time_min)<7) or (abs(os_train.loc[ind,'Date_received']-time_max)<7)):\n",
    "                    if ind not in indexxx:\n",
    "                        merchantid.add(os_train.loc[ind,'Merchant_id'])\n",
    "                    indexxx.extend([ind])\n",
    "                    indexs.extend([ind])\n",
    "            if len(merchantid)!=0:\n",
    "                indexs.extend(shopn(merchantid))\n",
    "                indexxx.extend([ind])\n",
    "    return indexs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "a=set(os_train['Merchant_id'].values).copy()\n",
    "shop_num=[]\n",
    "i=0\n",
    "os_train['shop_id']=0\n",
    "import winsound\n",
    "while True:\n",
    "    global indexxx\n",
    "    indexxx=[]\n",
    "    index1=shopn([list(a)[0]])\n",
    "    shop_num.append(index1)\n",
    "    os_train.loc[list(set(index1)),'shop_id']=i\n",
    "    for ind in set(os_train.loc[list(set(index1)),'Merchant_id'].values):\n",
    "        print(ind)\n",
    "        a.remove(ind)\n",
    "    if len(a)==0:\n",
    "        break\n",
    "    print('lenp(a):',len(a)/3697,'i:',i)\n",
    "    winsound.Beep(600,1000)\n",
    "    i+=1\n",
    "os_train_shop.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\os_train_shop.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "on_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\ccf_online_stage1_train\\ccf_online_stage1_train.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Action</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13740231</td>\n",
       "      <td>18907</td>\n",
       "      <td>2</td>\n",
       "      <td>100017492</td>\n",
       "      <td>500:50</td>\n",
       "      <td>20160513</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13740231</td>\n",
       "      <td>34805</td>\n",
       "      <td>1</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>14336199</td>\n",
       "      <td>38810</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>14336199</td>\n",
       "      <td>38810</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>14336199</td>\n",
       "      <td>38810</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14336199</td>\n",
       "      <td>38810</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>14336199</td>\n",
       "      <td>37005</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>14336199</td>\n",
       "      <td>14305</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>14336199</td>\n",
       "      <td>18907</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10539231</td>\n",
       "      <td>12008</td>\n",
       "      <td>1</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10539231</td>\n",
       "      <td>31904</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10539231</td>\n",
       "      <td>12008</td>\n",
       "      <td>1</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>14438631</td>\n",
       "      <td>55412</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>14438631</td>\n",
       "      <td>55412</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>14438631</td>\n",
       "      <td>38013</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>14438631</td>\n",
       "      <td>55412</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>14438631</td>\n",
       "      <td>58215</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>14438631</td>\n",
       "      <td>29814</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>14438631</td>\n",
       "      <td>55412</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>15034599</td>\n",
       "      <td>45003</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429796</th>\n",
       "      <td>12985299</td>\n",
       "      <td>24710</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429797</th>\n",
       "      <td>12985299</td>\n",
       "      <td>43413</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429798</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429799</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429800</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429801</th>\n",
       "      <td>12985299</td>\n",
       "      <td>17513</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429802</th>\n",
       "      <td>12985299</td>\n",
       "      <td>41503</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429803</th>\n",
       "      <td>12985299</td>\n",
       "      <td>41503</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429804</th>\n",
       "      <td>12985299</td>\n",
       "      <td>34211</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429805</th>\n",
       "      <td>12985299</td>\n",
       "      <td>47702</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429806</th>\n",
       "      <td>12985299</td>\n",
       "      <td>47702</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429807</th>\n",
       "      <td>12985299</td>\n",
       "      <td>44805</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429808</th>\n",
       "      <td>12985299</td>\n",
       "      <td>37815</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429809</th>\n",
       "      <td>12985299</td>\n",
       "      <td>37815</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429810</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>1</td>\n",
       "      <td>fixed</td>\n",
       "      <td>fixed</td>\n",
       "      <td>20160517</td>\n",
       "      <td>20160517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429811</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429812</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429813</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429814</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429815</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429816</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429817</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429818</th>\n",
       "      <td>12985299</td>\n",
       "      <td>49800</td>\n",
       "      <td>1</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429819</th>\n",
       "      <td>12985299</td>\n",
       "      <td>10813</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429820</th>\n",
       "      <td>13087731</td>\n",
       "      <td>52509</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429821</th>\n",
       "      <td>13087731</td>\n",
       "      <td>27715</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429822</th>\n",
       "      <td>13087731</td>\n",
       "      <td>52005</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429823</th>\n",
       "      <td>13087731</td>\n",
       "      <td>45611</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429824</th>\n",
       "      <td>13683699</td>\n",
       "      <td>18009</td>\n",
       "      <td>1</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11429825</th>\n",
       "      <td>13683699</td>\n",
       "      <td>18009</td>\n",
       "      <td>1</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>20160628</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11429826 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           User_id  Merchant_id  Action  Coupon_id Discount_rate  \\\n",
       "0         13740231        18907       2  100017492        500:50   \n",
       "1         13740231        34805       1       null          null   \n",
       "2         14336199        18907       0       null          null   \n",
       "3         14336199        18907       0       null          null   \n",
       "4         14336199        18907       0       null          null   \n",
       "5         14336199        18907       0       null          null   \n",
       "6         14336199        18907       0       null          null   \n",
       "7         14336199        18907       0       null          null   \n",
       "8         14336199        18907       0       null          null   \n",
       "9         14336199        18907       0       null          null   \n",
       "10        14336199        38810       0       null          null   \n",
       "11        14336199        38810       0       null          null   \n",
       "12        14336199        38810       0       null          null   \n",
       "13        14336199        38810       0       null          null   \n",
       "14        14336199        18907       0       null          null   \n",
       "15        14336199        18907       0       null          null   \n",
       "16        14336199        37005       0       null          null   \n",
       "17        14336199        14305       0       null          null   \n",
       "18        14336199        18907       0       null          null   \n",
       "19        10539231        12008       1       null          null   \n",
       "20        10539231        31904       0       null          null   \n",
       "21        10539231        12008       1       null          null   \n",
       "22        14438631        55412       0       null          null   \n",
       "23        14438631        55412       0       null          null   \n",
       "24        14438631        38013       0       null          null   \n",
       "25        14438631        55412       0       null          null   \n",
       "26        14438631        58215       0       null          null   \n",
       "27        14438631        29814       0       null          null   \n",
       "28        14438631        55412       0       null          null   \n",
       "29        15034599        45003       0       null          null   \n",
       "...            ...          ...     ...        ...           ...   \n",
       "11429796  12985299        24710       0       null          null   \n",
       "11429797  12985299        43413       0       null          null   \n",
       "11429798  12985299        49800       0       null          null   \n",
       "11429799  12985299        49800       0       null          null   \n",
       "11429800  12985299        49800       0       null          null   \n",
       "11429801  12985299        17513       0       null          null   \n",
       "11429802  12985299        41503       0       null          null   \n",
       "11429803  12985299        41503       0       null          null   \n",
       "11429804  12985299        34211       0       null          null   \n",
       "11429805  12985299        47702       0       null          null   \n",
       "11429806  12985299        47702       0       null          null   \n",
       "11429807  12985299        44805       0       null          null   \n",
       "11429808  12985299        37815       0       null          null   \n",
       "11429809  12985299        37815       0       null          null   \n",
       "11429810  12985299        49800       1      fixed         fixed   \n",
       "11429811  12985299        49800       0       null          null   \n",
       "11429812  12985299        49800       0       null          null   \n",
       "11429813  12985299        49800       0       null          null   \n",
       "11429814  12985299        49800       0       null          null   \n",
       "11429815  12985299        49800       0       null          null   \n",
       "11429816  12985299        49800       0       null          null   \n",
       "11429817  12985299        49800       0       null          null   \n",
       "11429818  12985299        49800       1       null          null   \n",
       "11429819  12985299        10813       0       null          null   \n",
       "11429820  13087731        52509       0       null          null   \n",
       "11429821  13087731        27715       0       null          null   \n",
       "11429822  13087731        52005       0       null          null   \n",
       "11429823  13087731        45611       0       null          null   \n",
       "11429824  13683699        18009       1       null          null   \n",
       "11429825  13683699        18009       1       null          null   \n",
       "\n",
       "         Date_received      Date  \n",
       "0             20160513      null  \n",
       "1                 null  20160321  \n",
       "2                 null  20160618  \n",
       "3                 null  20160618  \n",
       "4                 null  20160618  \n",
       "5                 null  20160618  \n",
       "6                 null  20160618  \n",
       "7                 null  20160618  \n",
       "8                 null  20160618  \n",
       "9                 null  20160618  \n",
       "10                null  20160126  \n",
       "11                null  20160126  \n",
       "12                null  20160126  \n",
       "13                null  20160126  \n",
       "14                null  20160127  \n",
       "15                null  20160127  \n",
       "16                null  20160412  \n",
       "17                null  20160127  \n",
       "18                null  20160618  \n",
       "19                null  20160618  \n",
       "20                null  20160107  \n",
       "21                null  20160618  \n",
       "22                null  20160630  \n",
       "23                null  20160630  \n",
       "24                null  20160614  \n",
       "25                null  20160629  \n",
       "26                null  20160421  \n",
       "27                null  20160530  \n",
       "28                null  20160630  \n",
       "29                null  20160411  \n",
       "...                ...       ...  \n",
       "11429796          null  20160415  \n",
       "11429797          null  20160229  \n",
       "11429798          null  20160517  \n",
       "11429799          null  20160517  \n",
       "11429800          null  20160520  \n",
       "11429801          null  20160329  \n",
       "11429802          null  20160410  \n",
       "11429803          null  20160410  \n",
       "11429804          null  20160419  \n",
       "11429805          null  20160323  \n",
       "11429806          null  20160323  \n",
       "11429807          null  20160415  \n",
       "11429808          null  20160323  \n",
       "11429809          null  20160323  \n",
       "11429810      20160517  20160517  \n",
       "11429811          null  20160516  \n",
       "11429812          null  20160516  \n",
       "11429813          null  20160516  \n",
       "11429814          null  20160516  \n",
       "11429815          null  20160516  \n",
       "11429816          null  20160516  \n",
       "11429817          null  20160516  \n",
       "11429818          null  20160520  \n",
       "11429819          null  20160414  \n",
       "11429820          null  20160609  \n",
       "11429821          null  20160629  \n",
       "11429822          null  20160324  \n",
       "11429823          null  20160422  \n",
       "11429824          null  20160323  \n",
       "11429825          null  20160628  \n",
       "\n",
       "[11429826 rows x 7 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "on_train#.head(60)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 增加用券在线购买和普通购买、领取优惠券、点击的列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\test_addordinarybuy.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "oftid_train['online_buy']=0#增加一列\n",
    "oftid_train['online_col']=0\n",
    "oftid_train['online_click']=0\n",
    "test['online_buy']=0#增加一列\n",
    "test['online_col']=0\n",
    "test['online_click']=0\n",
    "# odinary.index=np.arange(0,len(odinary),1)\n",
    "ontid_train.index=np.arange(0,len(ontid_train),1)\n",
    "oftid_train.index=np.arange(0,len(oftid_train),1)\n",
    "test.index=np.arange(0,len(test),1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "i=0\n",
    "j=10\n",
    "length=len(oftid_train)\n",
    "for index in oftid_train.index:\n",
    "    rate=int(i*100/length)#看进度\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%')\n",
    "        j=j+10\n",
    "\n",
    "    uid=oftid_train.iloc[index]['User_id']\n",
    "    if len(ontid_train[ontid_train['User_id']==uid].index)!=0:\n",
    "        for ind in ontid_train[ontid_train['User_id']==uid].index:\n",
    "            if ontid_train.iloc[ind]['Action']==1:\n",
    "                oftid_train.loc[index,'online_buy']+=1\n",
    "            elif ontid_train.iloc[ind]['Action']==2:\n",
    "                oftid_train.loc[index,'online_col']+=1\n",
    "            else:\n",
    "                oftid_train.loc[index,'online_click']+=1\n",
    "    i+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "oftid_train.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\ofon_coupod_buy_train.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "i=0\n",
    "j=10\n",
    "length=len(test)\n",
    "for index in test.index:\n",
    "    rate=int(i*100/length)#看进度\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%')\n",
    "        j=j+10\n",
    "\n",
    "    uid=test.iloc[index]['User_id']\n",
    "    if len(ontid_train[ontid_train['User_id']==uid].index)!=0:\n",
    "        for ind in ontid_train[ontid_train['User_id']==uid].index:\n",
    "            if ontid_train.iloc[ind]['Action']==1:\n",
    "                test.loc[index,'online_buy']+=1\n",
    "            elif ontid_train.iloc[ind]['Action']==2:\n",
    "                test.loc[index,'online_col']+=1\n",
    "            else:\n",
    "                test.loc[index,'online_click']+=1\n",
    "    i+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\test_ofon_coupod_buy.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "off_train=or_train.copy()\n",
    "test=or_test.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 导入增加几列以后的数据集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sys\n",
    "import seaborn as sns\n",
    "from time import sleep\n",
    "import matplotlib.pyplot as plt\n",
    "off_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\or_train.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\or_test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>ord_buy</th>\n",
       "      <th>Coupon_num</th>\n",
       "      <th>Coupon_nouse_num</th>\n",
       "      <th>Coupon_use_num</th>\n",
       "      <th>Coupon_use_prob</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>215</td>\n",
       "      <td>129</td>\n",
       "      <td>8944</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1</td>\n",
       "      <td>20160524</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>316</td>\n",
       "      <td>7974</td>\n",
       "      <td>8952</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0</td>\n",
       "      <td>20160430</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>417</td>\n",
       "      <td>3381</td>\n",
       "      <td>11951</td>\n",
       "      <td>180.0</td>\n",
       "      <td>0</td>\n",
       "      <td>20160227</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>417</td>\n",
       "      <td>450</td>\n",
       "      <td>8555</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1</td>\n",
       "      <td>20160203</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>417</td>\n",
       "      <td>775</td>\n",
       "      <td>5435</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0</td>\n",
       "      <td>20160329</td>\n",
       "      <td>20160412</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  Coupon_id  Discount_rate Distance Date_received  \\\n",
       "0      215          129       8944           25.0        1      20160524   \n",
       "1      316         7974       8952           40.0        0      20160430   \n",
       "2      417         3381      11951          180.0        0      20160227   \n",
       "3      417          450       8555           25.0        1      20160203   \n",
       "4      417          775       5435           25.0        0      20160329   \n",
       "\n",
       "       Date  ord_buy  Coupon_num  Coupon_nouse_num  Coupon_use_num  \\\n",
       "0      null        1           1                 1               0   \n",
       "1      null        0           1                 1               0   \n",
       "2      null        0           5                 4               1   \n",
       "3      null        0           5                 4               1   \n",
       "4  20160412        1           5                 4               1   \n",
       "\n",
       "   Coupon_use_prob  \n",
       "0              0.0  \n",
       "1              0.0  \n",
       "2              0.2  \n",
       "3              0.2  \n",
       "4              0.2  "
      ]
     },
     "execution_count": 296,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "off_train=off_train.sort_values(by=['User_id'])\n",
    "off_train['Date_received']=off_train['Date_received'].apply(lambda x: str(x))\n",
    "off_train.index=np.arange(0,len(off_train),1)\n",
    "off_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>ord_buy</th>\n",
       "      <th>Coupon_num</th>\n",
       "      <th>Coupon_nouse_num</th>\n",
       "      <th>Coupon_use_num</th>\n",
       "      <th>Coupon_use_prob</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4129537</td>\n",
       "      <td>450</td>\n",
       "      <td>9983</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1</td>\n",
       "      <td>20160712</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6949378</td>\n",
       "      <td>1300</td>\n",
       "      <td>3429</td>\n",
       "      <td>25.0</td>\n",
       "      <td>null</td>\n",
       "      <td>20160706</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2166529</td>\n",
       "      <td>7113</td>\n",
       "      <td>6928</td>\n",
       "      <td>180.0</td>\n",
       "      <td>5</td>\n",
       "      <td>20160727</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2166529</td>\n",
       "      <td>7113</td>\n",
       "      <td>1808</td>\n",
       "      <td>90.0</td>\n",
       "      <td>5</td>\n",
       "      <td>20160727</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6172162</td>\n",
       "      <td>7605</td>\n",
       "      <td>6500</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20160708</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  Coupon_id  Discount_rate Distance Date_received  \\\n",
       "0  4129537          450       9983           25.0        1      20160712   \n",
       "1  6949378         1300       3429           25.0     null      20160706   \n",
       "2  2166529         7113       6928          180.0        5      20160727   \n",
       "3  2166529         7113       1808           90.0        5      20160727   \n",
       "4  6172162         7605       6500           29.0        2      20160708   \n",
       "\n",
       "   ord_buy  Coupon_num  Coupon_nouse_num  Coupon_use_num  Coupon_use_prob  \n",
       "0        0           2                 2               0              0.0  \n",
       "1        0           1                 0               1              1.0  \n",
       "2        0           1                 1               0              0.0  \n",
       "3        0           1                 1               0              0.0  \n",
       "4        0           1                 1               0              0.0  "
      ]
     },
     "execution_count": 297,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def Discount_rate(x):#转化函数\n",
    "    if x.startswith('0'):\n",
    "        return float(x)\n",
    "    else:\n",
    "        return int(x.split(':')[0])-int(x.split(':')[1])\n",
    "#将满减转化为小数\n",
    "test['Discount_rate']=test['Discount_rate'].apply(Discount_rate)\n",
    "test['Date_received']=test['Date_received'].astype(np.int64)\n",
    "test['Date_received']=test['Date_received'].apply(lambda x: str(x))\n",
    "test.index=np.arange(0,len(test),1)\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 选取间隔日期大于15天，标记为负样本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "p_train=off_train[off_train['Date']!='null'].copy()#正样本\n",
    "n_train=off_train[off_train['Date']=='null'].copy()#负样本\n",
    "p_train.index=np.arange(0,len(p_train),1)\n",
    "n_train.index=np.arange(0,len(n_train),1)\n",
    "#转换为时间格式\n",
    "p_train['Date_received_copy']=p_train['Date_received'].copy()\n",
    "\n",
    "p_train['Date_received_copy']=pd.to_datetime(p_train['Date_received_copy'])\n",
    "p_train['Date']=pd.to_datetime(p_train['Date'])\n",
    "\n",
    "p_train['date']=(p_train['Date']-p_train['Date_received_copy']).astype('timedelta64[D]')\n",
    "p_train.loc[p_train[p_train['date']<=15].index,'date']=1\n",
    "p_train.loc[p_train[p_train['date']>15].index,'date']=0\n",
    "p_train['Date']=p_train['date'].copy()\n",
    "p_train.drop(['date','Date_received_copy'],axis=1,inplace=True)\n",
    "p_train['Date']=p_train['Date'].astype(np.int64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#转换为星期几x.weekday()+1\n",
    "test['Date_received']=(test['Date_received'].apply(lambda x: int(x[5:8]))).copy()\n",
    "#正样本为1，负样本为0\n",
    "n_train['Date']=0\n",
    "train=pd.concat([p_train,n_train],axis=0)\n",
    "train['Date_received']=(train['Date_received'].apply(lambda x: int(x[5:8]))).copy()\n",
    "train['Date_received']=train['Date_received'].astype(np.int64)\n",
    "train=train.sort_values(by=['User_id'])\n",
    "train.index=np.arange(0,len(train),1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 转化满减为小数折扣"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 300,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train=train.replace('null',np.nan)\n",
    "test=test.replace('null',np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train=train.fillna(method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train['Distance']=train['Distance'].astype(np.int64)\n",
    "train['Coupon_id']=train['Coupon_id'].astype(np.int64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 216250 entries, 0 to 216249\n",
      "Data columns (total 12 columns):\n",
      "User_id             216250 non-null int64\n",
      "Merchant_id         216250 non-null int64\n",
      "Coupon_id           216250 non-null int64\n",
      "Discount_rate       216250 non-null float64\n",
      "Distance            216250 non-null int64\n",
      "Date_received       216250 non-null int64\n",
      "Date                216250 non-null int64\n",
      "ord_buy             216250 non-null int64\n",
      "Coupon_num          216250 non-null int64\n",
      "Coupon_nouse_num    216250 non-null int64\n",
      "Coupon_use_num      216250 non-null int64\n",
      "Coupon_use_prob     216250 non-null float64\n",
      "dtypes: float64(2), int64(10)\n",
      "memory usage: 21.4 MB\n"
     ]
    }
   ],
   "source": [
    "train.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 不同类型数据的影响"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train['Merchant_id']=train['Merchant_id'].astype(np.object)\n",
    "train['User_id']=train['User_id'].astype(np.object)\n",
    "train['Date_received']=train['Date_received'].astype('category')\n",
    "test['Merchant_id']=test['Merchant_id'].astype(np.object)\n",
    "test['Coupon_id']=test['Coupon_id'].astype(np.object)\n",
    "test['Distance']=test['Distance'].astype(np.object)\n",
    "test['Date_received']=test['Date_received'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test['User_id']=test['User_id'].astype(np.object)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充测试集的空缺值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test=test.fillna(method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 305,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test['Merchant_id']=test['Merchant_id'].astype(np.int64)\n",
    "test['Coupon_id']=test['Coupon_id'].astype(np.int64)\n",
    "test['Distance']=test['Distance'].astype(np.int64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 113640 entries, 0 to 113639\n",
      "Data columns (total 11 columns):\n",
      "User_id             113640 non-null int64\n",
      "Merchant_id         113640 non-null int64\n",
      "Coupon_id           113640 non-null int64\n",
      "Discount_rate       113640 non-null float64\n",
      "Distance            113640 non-null int64\n",
      "Date_received       113640 non-null int64\n",
      "ord_buy             113640 non-null int64\n",
      "Coupon_num          113640 non-null int64\n",
      "Coupon_nouse_num    113640 non-null int64\n",
      "Coupon_use_num      113640 non-null int64\n",
      "Coupon_use_prob     113640 non-null float64\n",
      "dtypes: float64(2), int64(9)\n",
      "memory usage: 10.4 MB\n"
     ]
    }
   ],
   "source": [
    "test.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#行最大最小归一化\n",
    "XX=train.copy()\n",
    "for col in ['Merchant_id','Coupon_id']:\n",
    "    max=XX[col].max()\n",
    "    min=XX[col].min()\n",
    "    XX[col]=XX[col].apply(lambda x: ((x-min)/(max-min)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "XX.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cor=XX.corr()\n",
    "cor[cor<0.01]=0\n",
    "cor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 距离统计分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import seaborn as sns \n",
    "%matplotlib inline\n",
    "\n",
    "sns.countplot(x='Distance',hue='Date',data=train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "%matplotlib inline\n",
    "\n",
    "sns.countplot(x='Date_received',hue='Date',data=train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import seaborn as sns \n",
    "%matplotlib inline\n",
    "plt.figure(figsize=(10,6))\n",
    "sns.countplot(x='Discount_rate',hue='Date',data=train)\n",
    "plt.xticks(rotation=90)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "a=set(train['Coupon_id'].values)\n",
    "c=set(test['Coupon_id'].values)\n",
    "len(c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 357,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>ord_buy</th>\n",
       "      <th>Coupon_num</th>\n",
       "      <th>Coupon_nouse_num</th>\n",
       "      <th>...</th>\n",
       "      <th>Coupon_lessthan100</th>\n",
       "      <th>Coupon_lep100</th>\n",
       "      <th>Coupon_grp100</th>\n",
       "      <th>Avg_saler_Coupon</th>\n",
       "      <th>Avg_distance</th>\n",
       "      <th>shop_count</th>\n",
       "      <th>shop_pro</th>\n",
       "      <th>Coupon_diffcount</th>\n",
       "      <th>Coupon_diffpro</th>\n",
       "      <th>same_Coupon_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>User_id</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.004005</td>\n",
       "      <td>-0.001469</td>\n",
       "      <td>-0.003272</td>\n",
       "      <td>0.000964</td>\n",
       "      <td>-0.002444</td>\n",
       "      <td>0.000421</td>\n",
       "      <td>-0.006952</td>\n",
       "      <td>-0.017397</td>\n",
       "      <td>-0.011613</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.016907</td>\n",
       "      <td>-0.003299</td>\n",
       "      <td>-0.000303</td>\n",
       "      <td>-0.016830</td>\n",
       "      <td>0.006692</td>\n",
       "      <td>-0.002044</td>\n",
       "      <td>-0.003171</td>\n",
       "      <td>-0.003258</td>\n",
       "      <td>-0.002287</td>\n",
       "      <td>-0.017042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Merchant_id</th>\n",
       "      <td>0.004005</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.138292</td>\n",
       "      <td>-0.008626</td>\n",
       "      <td>-0.003292</td>\n",
       "      <td>-0.137270</td>\n",
       "      <td>0.057725</td>\n",
       "      <td>0.080102</td>\n",
       "      <td>0.078623</td>\n",
       "      <td>0.044146</td>\n",
       "      <td>...</td>\n",
       "      <td>0.076124</td>\n",
       "      <td>0.063998</td>\n",
       "      <td>0.007644</td>\n",
       "      <td>0.080026</td>\n",
       "      <td>0.031900</td>\n",
       "      <td>0.060801</td>\n",
       "      <td>0.087671</td>\n",
       "      <td>0.074345</td>\n",
       "      <td>0.074265</td>\n",
       "      <td>0.073047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_id</th>\n",
       "      <td>-0.001469</td>\n",
       "      <td>0.138292</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.134820</td>\n",
       "      <td>0.045436</td>\n",
       "      <td>-0.145048</td>\n",
       "      <td>0.026200</td>\n",
       "      <td>0.055565</td>\n",
       "      <td>0.054967</td>\n",
       "      <td>0.039055</td>\n",
       "      <td>...</td>\n",
       "      <td>0.042967</td>\n",
       "      <td>0.034292</td>\n",
       "      <td>0.008179</td>\n",
       "      <td>0.042435</td>\n",
       "      <td>0.021788</td>\n",
       "      <td>0.034097</td>\n",
       "      <td>0.041426</td>\n",
       "      <td>0.041838</td>\n",
       "      <td>0.022016</td>\n",
       "      <td>0.055806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Discount_rate</th>\n",
       "      <td>-0.003272</td>\n",
       "      <td>-0.008626</td>\n",
       "      <td>0.134820</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.123656</td>\n",
       "      <td>-0.287202</td>\n",
       "      <td>-0.151889</td>\n",
       "      <td>-0.143597</td>\n",
       "      <td>-0.042159</td>\n",
       "      <td>0.008198</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.081950</td>\n",
       "      <td>-0.150810</td>\n",
       "      <td>0.069996</td>\n",
       "      <td>-0.082522</td>\n",
       "      <td>-0.046208</td>\n",
       "      <td>-0.130015</td>\n",
       "      <td>-0.170790</td>\n",
       "      <td>-0.120311</td>\n",
       "      <td>-0.189446</td>\n",
       "      <td>-0.096258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Distance</th>\n",
       "      <td>0.000964</td>\n",
       "      <td>-0.003292</td>\n",
       "      <td>0.045436</td>\n",
       "      <td>0.123656</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.168149</td>\n",
       "      <td>-0.126451</td>\n",
       "      <td>-0.184345</td>\n",
       "      <td>-0.044554</td>\n",
       "      <td>0.005429</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.075609</td>\n",
       "      <td>-0.124763</td>\n",
       "      <td>-0.007844</td>\n",
       "      <td>-0.092196</td>\n",
       "      <td>0.195608</td>\n",
       "      <td>-0.115968</td>\n",
       "      <td>-0.212422</td>\n",
       "      <td>-0.125490</td>\n",
       "      <td>-0.147698</td>\n",
       "      <td>-0.081809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date_received</th>\n",
       "      <td>-0.002444</td>\n",
       "      <td>-0.137270</td>\n",
       "      <td>-0.145048</td>\n",
       "      <td>-0.287202</td>\n",
       "      <td>-0.168149</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.092681</td>\n",
       "      <td>0.090143</td>\n",
       "      <td>-0.076489</td>\n",
       "      <td>-0.147139</td>\n",
       "      <td>...</td>\n",
       "      <td>0.055123</td>\n",
       "      <td>0.084282</td>\n",
       "      <td>-0.023080</td>\n",
       "      <td>0.061119</td>\n",
       "      <td>0.048020</td>\n",
       "      <td>0.066962</td>\n",
       "      <td>0.120387</td>\n",
       "      <td>0.063099</td>\n",
       "      <td>0.123834</td>\n",
       "      <td>0.056793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>0.000421</td>\n",
       "      <td>0.057725</td>\n",
       "      <td>0.026200</td>\n",
       "      <td>-0.151889</td>\n",
       "      <td>-0.126451</td>\n",
       "      <td>0.092681</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.269598</td>\n",
       "      <td>0.160456</td>\n",
       "      <td>-0.061029</td>\n",
       "      <td>...</td>\n",
       "      <td>0.338986</td>\n",
       "      <td>0.519031</td>\n",
       "      <td>0.062136</td>\n",
       "      <td>0.347783</td>\n",
       "      <td>0.187342</td>\n",
       "      <td>0.504734</td>\n",
       "      <td>0.624277</td>\n",
       "      <td>0.497702</td>\n",
       "      <td>0.726871</td>\n",
       "      <td>0.307410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ord_buy</th>\n",
       "      <td>-0.006952</td>\n",
       "      <td>0.080102</td>\n",
       "      <td>0.055565</td>\n",
       "      <td>-0.143597</td>\n",
       "      <td>-0.184345</td>\n",
       "      <td>0.090143</td>\n",
       "      <td>0.269598</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.440441</td>\n",
       "      <td>0.270713</td>\n",
       "      <td>...</td>\n",
       "      <td>0.372477</td>\n",
       "      <td>0.260801</td>\n",
       "      <td>0.035590</td>\n",
       "      <td>0.404596</td>\n",
       "      <td>0.008838</td>\n",
       "      <td>0.259122</td>\n",
       "      <td>0.346577</td>\n",
       "      <td>0.347479</td>\n",
       "      <td>0.303077</td>\n",
       "      <td>0.479700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_num</th>\n",
       "      <td>-0.017397</td>\n",
       "      <td>0.078623</td>\n",
       "      <td>0.054967</td>\n",
       "      <td>-0.042159</td>\n",
       "      <td>-0.044554</td>\n",
       "      <td>-0.076489</td>\n",
       "      <td>0.160456</td>\n",
       "      <td>0.440441</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.801607</td>\n",
       "      <td>...</td>\n",
       "      <td>0.649425</td>\n",
       "      <td>0.299203</td>\n",
       "      <td>0.066784</td>\n",
       "      <td>0.641447</td>\n",
       "      <td>0.086869</td>\n",
       "      <td>0.333093</td>\n",
       "      <td>0.279390</td>\n",
       "      <td>0.472562</td>\n",
       "      <td>0.199977</td>\n",
       "      <td>0.619247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_nouse_num</th>\n",
       "      <td>-0.011613</td>\n",
       "      <td>0.044146</td>\n",
       "      <td>0.039055</td>\n",
       "      <td>0.008198</td>\n",
       "      <td>0.005429</td>\n",
       "      <td>-0.147139</td>\n",
       "      <td>-0.061029</td>\n",
       "      <td>0.270713</td>\n",
       "      <td>0.801607</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.076443</td>\n",
       "      <td>0.116678</td>\n",
       "      <td>0.056039</td>\n",
       "      <td>0.081559</td>\n",
       "      <td>0.036772</td>\n",
       "      <td>0.144314</td>\n",
       "      <td>0.081627</td>\n",
       "      <td>0.208460</td>\n",
       "      <td>0.004079</td>\n",
       "      <td>0.229332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_use_num</th>\n",
       "      <td>-0.014809</td>\n",
       "      <td>0.077044</td>\n",
       "      <td>0.043928</td>\n",
       "      <td>-0.079962</td>\n",
       "      <td>-0.080582</td>\n",
       "      <td>0.051406</td>\n",
       "      <td>0.340507</td>\n",
       "      <td>0.403199</td>\n",
       "      <td>0.688701</td>\n",
       "      <td>0.118598</td>\n",
       "      <td>...</td>\n",
       "      <td>0.985893</td>\n",
       "      <td>0.355431</td>\n",
       "      <td>0.042957</td>\n",
       "      <td>0.966438</td>\n",
       "      <td>0.099681</td>\n",
       "      <td>0.378201</td>\n",
       "      <td>0.365032</td>\n",
       "      <td>0.532045</td>\n",
       "      <td>0.327184</td>\n",
       "      <td>0.750353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_use_prob</th>\n",
       "      <td>0.001104</td>\n",
       "      <td>0.062127</td>\n",
       "      <td>0.033917</td>\n",
       "      <td>-0.149934</td>\n",
       "      <td>-0.134545</td>\n",
       "      <td>0.120601</td>\n",
       "      <td>0.638473</td>\n",
       "      <td>0.299444</td>\n",
       "      <td>0.234312</td>\n",
       "      <td>-0.094139</td>\n",
       "      <td>...</td>\n",
       "      <td>0.474332</td>\n",
       "      <td>0.708285</td>\n",
       "      <td>0.083492</td>\n",
       "      <td>0.439189</td>\n",
       "      <td>0.266936</td>\n",
       "      <td>0.718752</td>\n",
       "      <td>0.696524</td>\n",
       "      <td>0.708090</td>\n",
       "      <td>0.632464</td>\n",
       "      <td>0.409788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_greaterthan100</th>\n",
       "      <td>0.009439</td>\n",
       "      <td>0.008751</td>\n",
       "      <td>-0.003408</td>\n",
       "      <td>0.030125</td>\n",
       "      <td>-0.018614</td>\n",
       "      <td>-0.023318</td>\n",
       "      <td>0.046796</td>\n",
       "      <td>0.209286</td>\n",
       "      <td>0.274719</td>\n",
       "      <td>0.250231</td>\n",
       "      <td>...</td>\n",
       "      <td>0.044565</td>\n",
       "      <td>-0.000873</td>\n",
       "      <td>0.444626</td>\n",
       "      <td>0.151991</td>\n",
       "      <td>0.023161</td>\n",
       "      <td>0.084567</td>\n",
       "      <td>0.081103</td>\n",
       "      <td>0.225569</td>\n",
       "      <td>0.024979</td>\n",
       "      <td>0.313558</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_lessthan100</th>\n",
       "      <td>-0.016907</td>\n",
       "      <td>0.076124</td>\n",
       "      <td>0.042967</td>\n",
       "      <td>-0.081950</td>\n",
       "      <td>-0.075609</td>\n",
       "      <td>0.055123</td>\n",
       "      <td>0.338986</td>\n",
       "      <td>0.372477</td>\n",
       "      <td>0.649425</td>\n",
       "      <td>0.076443</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.361890</td>\n",
       "      <td>-0.004647</td>\n",
       "      <td>0.970497</td>\n",
       "      <td>0.093687</td>\n",
       "      <td>0.358408</td>\n",
       "      <td>0.348408</td>\n",
       "      <td>0.500232</td>\n",
       "      <td>0.319180</td>\n",
       "      <td>0.716174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_lep100</th>\n",
       "      <td>-0.003299</td>\n",
       "      <td>0.063998</td>\n",
       "      <td>0.034292</td>\n",
       "      <td>-0.150810</td>\n",
       "      <td>-0.124763</td>\n",
       "      <td>0.084282</td>\n",
       "      <td>0.519031</td>\n",
       "      <td>0.260801</td>\n",
       "      <td>0.299203</td>\n",
       "      <td>0.116678</td>\n",
       "      <td>...</td>\n",
       "      <td>0.361890</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.035777</td>\n",
       "      <td>0.303198</td>\n",
       "      <td>0.350655</td>\n",
       "      <td>0.921439</td>\n",
       "      <td>0.786657</td>\n",
       "      <td>0.826996</td>\n",
       "      <td>0.619998</td>\n",
       "      <td>0.374660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_grp100</th>\n",
       "      <td>-0.000303</td>\n",
       "      <td>0.007644</td>\n",
       "      <td>0.008179</td>\n",
       "      <td>0.069996</td>\n",
       "      <td>-0.007844</td>\n",
       "      <td>-0.023080</td>\n",
       "      <td>0.062136</td>\n",
       "      <td>0.035590</td>\n",
       "      <td>0.066784</td>\n",
       "      <td>0.056039</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.004647</td>\n",
       "      <td>-0.035777</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.034706</td>\n",
       "      <td>0.086105</td>\n",
       "      <td>0.149698</td>\n",
       "      <td>0.105225</td>\n",
       "      <td>0.155300</td>\n",
       "      <td>0.052358</td>\n",
       "      <td>0.072442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Avg_saler_Coupon</th>\n",
       "      <td>-0.016830</td>\n",
       "      <td>0.080026</td>\n",
       "      <td>0.042435</td>\n",
       "      <td>-0.082522</td>\n",
       "      <td>-0.092196</td>\n",
       "      <td>0.061119</td>\n",
       "      <td>0.347783</td>\n",
       "      <td>0.404596</td>\n",
       "      <td>0.641447</td>\n",
       "      <td>0.081559</td>\n",
       "      <td>...</td>\n",
       "      <td>0.970497</td>\n",
       "      <td>0.303198</td>\n",
       "      <td>0.034706</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.064118</td>\n",
       "      <td>0.290535</td>\n",
       "      <td>0.373789</td>\n",
       "      <td>0.458448</td>\n",
       "      <td>0.314521</td>\n",
       "      <td>0.745392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Avg_distance</th>\n",
       "      <td>0.006692</td>\n",
       "      <td>0.031900</td>\n",
       "      <td>0.021788</td>\n",
       "      <td>-0.046208</td>\n",
       "      <td>0.195608</td>\n",
       "      <td>0.048020</td>\n",
       "      <td>0.187342</td>\n",
       "      <td>0.008838</td>\n",
       "      <td>0.086869</td>\n",
       "      <td>0.036772</td>\n",
       "      <td>...</td>\n",
       "      <td>0.093687</td>\n",
       "      <td>0.350655</td>\n",
       "      <td>0.086105</td>\n",
       "      <td>0.064118</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.379171</td>\n",
       "      <td>0.258987</td>\n",
       "      <td>0.295469</td>\n",
       "      <td>0.243625</td>\n",
       "      <td>0.134220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shop_count</th>\n",
       "      <td>-0.002044</td>\n",
       "      <td>0.060801</td>\n",
       "      <td>0.034097</td>\n",
       "      <td>-0.130015</td>\n",
       "      <td>-0.115968</td>\n",
       "      <td>0.066962</td>\n",
       "      <td>0.504734</td>\n",
       "      <td>0.259122</td>\n",
       "      <td>0.333093</td>\n",
       "      <td>0.144314</td>\n",
       "      <td>...</td>\n",
       "      <td>0.358408</td>\n",
       "      <td>0.921439</td>\n",
       "      <td>0.149698</td>\n",
       "      <td>0.290535</td>\n",
       "      <td>0.379171</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.750713</td>\n",
       "      <td>0.875626</td>\n",
       "      <td>0.629895</td>\n",
       "      <td>0.376095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shop_pro</th>\n",
       "      <td>-0.003171</td>\n",
       "      <td>0.087671</td>\n",
       "      <td>0.041426</td>\n",
       "      <td>-0.170790</td>\n",
       "      <td>-0.212422</td>\n",
       "      <td>0.120387</td>\n",
       "      <td>0.624277</td>\n",
       "      <td>0.346577</td>\n",
       "      <td>0.279390</td>\n",
       "      <td>0.081627</td>\n",
       "      <td>...</td>\n",
       "      <td>0.348408</td>\n",
       "      <td>0.786657</td>\n",
       "      <td>0.105225</td>\n",
       "      <td>0.373789</td>\n",
       "      <td>0.258987</td>\n",
       "      <td>0.750713</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.717169</td>\n",
       "      <td>0.743497</td>\n",
       "      <td>0.414616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_diffcount</th>\n",
       "      <td>-0.003258</td>\n",
       "      <td>0.074345</td>\n",
       "      <td>0.041838</td>\n",
       "      <td>-0.120311</td>\n",
       "      <td>-0.125490</td>\n",
       "      <td>0.063099</td>\n",
       "      <td>0.497702</td>\n",
       "      <td>0.347479</td>\n",
       "      <td>0.472562</td>\n",
       "      <td>0.208460</td>\n",
       "      <td>...</td>\n",
       "      <td>0.500232</td>\n",
       "      <td>0.826996</td>\n",
       "      <td>0.155300</td>\n",
       "      <td>0.458448</td>\n",
       "      <td>0.295469</td>\n",
       "      <td>0.875626</td>\n",
       "      <td>0.717169</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.520782</td>\n",
       "      <td>0.461961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon_diffpro</th>\n",
       "      <td>-0.002287</td>\n",
       "      <td>0.074265</td>\n",
       "      <td>0.022016</td>\n",
       "      <td>-0.189446</td>\n",
       "      <td>-0.147698</td>\n",
       "      <td>0.123834</td>\n",
       "      <td>0.726871</td>\n",
       "      <td>0.303077</td>\n",
       "      <td>0.199977</td>\n",
       "      <td>0.004079</td>\n",
       "      <td>...</td>\n",
       "      <td>0.319180</td>\n",
       "      <td>0.619998</td>\n",
       "      <td>0.052358</td>\n",
       "      <td>0.314521</td>\n",
       "      <td>0.243625</td>\n",
       "      <td>0.629895</td>\n",
       "      <td>0.743497</td>\n",
       "      <td>0.520782</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.387775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>same_Coupon_count</th>\n",
       "      <td>-0.017042</td>\n",
       "      <td>0.073047</td>\n",
       "      <td>0.055806</td>\n",
       "      <td>-0.096258</td>\n",
       "      <td>-0.081809</td>\n",
       "      <td>0.056793</td>\n",
       "      <td>0.307410</td>\n",
       "      <td>0.479700</td>\n",
       "      <td>0.619247</td>\n",
       "      <td>0.229332</td>\n",
       "      <td>...</td>\n",
       "      <td>0.716174</td>\n",
       "      <td>0.374660</td>\n",
       "      <td>0.072442</td>\n",
       "      <td>0.745392</td>\n",
       "      <td>0.134220</td>\n",
       "      <td>0.376095</td>\n",
       "      <td>0.414616</td>\n",
       "      <td>0.461961</td>\n",
       "      <td>0.387775</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        User_id  Merchant_id  Coupon_id  Discount_rate  \\\n",
       "User_id                1.000000     0.004005  -0.001469      -0.003272   \n",
       "Merchant_id            0.004005     1.000000   0.138292      -0.008626   \n",
       "Coupon_id             -0.001469     0.138292   1.000000       0.134820   \n",
       "Discount_rate         -0.003272    -0.008626   0.134820       1.000000   \n",
       "Distance               0.000964    -0.003292   0.045436       0.123656   \n",
       "Date_received         -0.002444    -0.137270  -0.145048      -0.287202   \n",
       "Date                   0.000421     0.057725   0.026200      -0.151889   \n",
       "ord_buy               -0.006952     0.080102   0.055565      -0.143597   \n",
       "Coupon_num            -0.017397     0.078623   0.054967      -0.042159   \n",
       "Coupon_nouse_num      -0.011613     0.044146   0.039055       0.008198   \n",
       "Coupon_use_num        -0.014809     0.077044   0.043928      -0.079962   \n",
       "Coupon_use_prob        0.001104     0.062127   0.033917      -0.149934   \n",
       "Coupon_greaterthan100  0.009439     0.008751  -0.003408       0.030125   \n",
       "Coupon_lessthan100    -0.016907     0.076124   0.042967      -0.081950   \n",
       "Coupon_lep100         -0.003299     0.063998   0.034292      -0.150810   \n",
       "Coupon_grp100         -0.000303     0.007644   0.008179       0.069996   \n",
       "Avg_saler_Coupon      -0.016830     0.080026   0.042435      -0.082522   \n",
       "Avg_distance           0.006692     0.031900   0.021788      -0.046208   \n",
       "shop_count            -0.002044     0.060801   0.034097      -0.130015   \n",
       "shop_pro              -0.003171     0.087671   0.041426      -0.170790   \n",
       "Coupon_diffcount      -0.003258     0.074345   0.041838      -0.120311   \n",
       "Coupon_diffpro        -0.002287     0.074265   0.022016      -0.189446   \n",
       "same_Coupon_count     -0.017042     0.073047   0.055806      -0.096258   \n",
       "\n",
       "                       Distance  Date_received      Date   ord_buy  \\\n",
       "User_id                0.000964      -0.002444  0.000421 -0.006952   \n",
       "Merchant_id           -0.003292      -0.137270  0.057725  0.080102   \n",
       "Coupon_id              0.045436      -0.145048  0.026200  0.055565   \n",
       "Discount_rate          0.123656      -0.287202 -0.151889 -0.143597   \n",
       "Distance               1.000000      -0.168149 -0.126451 -0.184345   \n",
       "Date_received         -0.168149       1.000000  0.092681  0.090143   \n",
       "Date                  -0.126451       0.092681  1.000000  0.269598   \n",
       "ord_buy               -0.184345       0.090143  0.269598  1.000000   \n",
       "Coupon_num            -0.044554      -0.076489  0.160456  0.440441   \n",
       "Coupon_nouse_num       0.005429      -0.147139 -0.061029  0.270713   \n",
       "Coupon_use_num        -0.080582       0.051406  0.340507  0.403199   \n",
       "Coupon_use_prob       -0.134545       0.120601  0.638473  0.299444   \n",
       "Coupon_greaterthan100 -0.018614      -0.023318  0.046796  0.209286   \n",
       "Coupon_lessthan100    -0.075609       0.055123  0.338986  0.372477   \n",
       "Coupon_lep100         -0.124763       0.084282  0.519031  0.260801   \n",
       "Coupon_grp100         -0.007844      -0.023080  0.062136  0.035590   \n",
       "Avg_saler_Coupon      -0.092196       0.061119  0.347783  0.404596   \n",
       "Avg_distance           0.195608       0.048020  0.187342  0.008838   \n",
       "shop_count            -0.115968       0.066962  0.504734  0.259122   \n",
       "shop_pro              -0.212422       0.120387  0.624277  0.346577   \n",
       "Coupon_diffcount      -0.125490       0.063099  0.497702  0.347479   \n",
       "Coupon_diffpro        -0.147698       0.123834  0.726871  0.303077   \n",
       "same_Coupon_count     -0.081809       0.056793  0.307410  0.479700   \n",
       "\n",
       "                       Coupon_num  Coupon_nouse_num        ...          \\\n",
       "User_id                 -0.017397         -0.011613        ...           \n",
       "Merchant_id              0.078623          0.044146        ...           \n",
       "Coupon_id                0.054967          0.039055        ...           \n",
       "Discount_rate           -0.042159          0.008198        ...           \n",
       "Distance                -0.044554          0.005429        ...           \n",
       "Date_received           -0.076489         -0.147139        ...           \n",
       "Date                     0.160456         -0.061029        ...           \n",
       "ord_buy                  0.440441          0.270713        ...           \n",
       "Coupon_num               1.000000          0.801607        ...           \n",
       "Coupon_nouse_num         0.801607          1.000000        ...           \n",
       "Coupon_use_num           0.688701          0.118598        ...           \n",
       "Coupon_use_prob          0.234312         -0.094139        ...           \n",
       "Coupon_greaterthan100    0.274719          0.250231        ...           \n",
       "Coupon_lessthan100       0.649425          0.076443        ...           \n",
       "Coupon_lep100            0.299203          0.116678        ...           \n",
       "Coupon_grp100            0.066784          0.056039        ...           \n",
       "Avg_saler_Coupon         0.641447          0.081559        ...           \n",
       "Avg_distance             0.086869          0.036772        ...           \n",
       "shop_count               0.333093          0.144314        ...           \n",
       "shop_pro                 0.279390          0.081627        ...           \n",
       "Coupon_diffcount         0.472562          0.208460        ...           \n",
       "Coupon_diffpro           0.199977          0.004079        ...           \n",
       "same_Coupon_count        0.619247          0.229332        ...           \n",
       "\n",
       "                       Coupon_lessthan100  Coupon_lep100  Coupon_grp100  \\\n",
       "User_id                         -0.016907      -0.003299      -0.000303   \n",
       "Merchant_id                      0.076124       0.063998       0.007644   \n",
       "Coupon_id                        0.042967       0.034292       0.008179   \n",
       "Discount_rate                   -0.081950      -0.150810       0.069996   \n",
       "Distance                        -0.075609      -0.124763      -0.007844   \n",
       "Date_received                    0.055123       0.084282      -0.023080   \n",
       "Date                             0.338986       0.519031       0.062136   \n",
       "ord_buy                          0.372477       0.260801       0.035590   \n",
       "Coupon_num                       0.649425       0.299203       0.066784   \n",
       "Coupon_nouse_num                 0.076443       0.116678       0.056039   \n",
       "Coupon_use_num                   0.985893       0.355431       0.042957   \n",
       "Coupon_use_prob                  0.474332       0.708285       0.083492   \n",
       "Coupon_greaterthan100            0.044565      -0.000873       0.444626   \n",
       "Coupon_lessthan100               1.000000       0.361890      -0.004647   \n",
       "Coupon_lep100                    0.361890       1.000000      -0.035777   \n",
       "Coupon_grp100                   -0.004647      -0.035777       1.000000   \n",
       "Avg_saler_Coupon                 0.970497       0.303198       0.034706   \n",
       "Avg_distance                     0.093687       0.350655       0.086105   \n",
       "shop_count                       0.358408       0.921439       0.149698   \n",
       "shop_pro                         0.348408       0.786657       0.105225   \n",
       "Coupon_diffcount                 0.500232       0.826996       0.155300   \n",
       "Coupon_diffpro                   0.319180       0.619998       0.052358   \n",
       "same_Coupon_count                0.716174       0.374660       0.072442   \n",
       "\n",
       "                       Avg_saler_Coupon  Avg_distance  shop_count  shop_pro  \\\n",
       "User_id                       -0.016830      0.006692   -0.002044 -0.003171   \n",
       "Merchant_id                    0.080026      0.031900    0.060801  0.087671   \n",
       "Coupon_id                      0.042435      0.021788    0.034097  0.041426   \n",
       "Discount_rate                 -0.082522     -0.046208   -0.130015 -0.170790   \n",
       "Distance                      -0.092196      0.195608   -0.115968 -0.212422   \n",
       "Date_received                  0.061119      0.048020    0.066962  0.120387   \n",
       "Date                           0.347783      0.187342    0.504734  0.624277   \n",
       "ord_buy                        0.404596      0.008838    0.259122  0.346577   \n",
       "Coupon_num                     0.641447      0.086869    0.333093  0.279390   \n",
       "Coupon_nouse_num               0.081559      0.036772    0.144314  0.081627   \n",
       "Coupon_use_num                 0.966438      0.099681    0.378201  0.365032   \n",
       "Coupon_use_prob                0.439189      0.266936    0.718752  0.696524   \n",
       "Coupon_greaterthan100          0.151991      0.023161    0.084567  0.081103   \n",
       "Coupon_lessthan100             0.970497      0.093687    0.358408  0.348408   \n",
       "Coupon_lep100                  0.303198      0.350655    0.921439  0.786657   \n",
       "Coupon_grp100                  0.034706      0.086105    0.149698  0.105225   \n",
       "Avg_saler_Coupon               1.000000      0.064118    0.290535  0.373789   \n",
       "Avg_distance                   0.064118      1.000000    0.379171  0.258987   \n",
       "shop_count                     0.290535      0.379171    1.000000  0.750713   \n",
       "shop_pro                       0.373789      0.258987    0.750713  1.000000   \n",
       "Coupon_diffcount               0.458448      0.295469    0.875626  0.717169   \n",
       "Coupon_diffpro                 0.314521      0.243625    0.629895  0.743497   \n",
       "same_Coupon_count              0.745392      0.134220    0.376095  0.414616   \n",
       "\n",
       "                       Coupon_diffcount  Coupon_diffpro  same_Coupon_count  \n",
       "User_id                       -0.003258       -0.002287          -0.017042  \n",
       "Merchant_id                    0.074345        0.074265           0.073047  \n",
       "Coupon_id                      0.041838        0.022016           0.055806  \n",
       "Discount_rate                 -0.120311       -0.189446          -0.096258  \n",
       "Distance                      -0.125490       -0.147698          -0.081809  \n",
       "Date_received                  0.063099        0.123834           0.056793  \n",
       "Date                           0.497702        0.726871           0.307410  \n",
       "ord_buy                        0.347479        0.303077           0.479700  \n",
       "Coupon_num                     0.472562        0.199977           0.619247  \n",
       "Coupon_nouse_num               0.208460        0.004079           0.229332  \n",
       "Coupon_use_num                 0.532045        0.327184           0.750353  \n",
       "Coupon_use_prob                0.708090        0.632464           0.409788  \n",
       "Coupon_greaterthan100          0.225569        0.024979           0.313558  \n",
       "Coupon_lessthan100             0.500232        0.319180           0.716174  \n",
       "Coupon_lep100                  0.826996        0.619998           0.374660  \n",
       "Coupon_grp100                  0.155300        0.052358           0.072442  \n",
       "Avg_saler_Coupon               0.458448        0.314521           0.745392  \n",
       "Avg_distance                   0.295469        0.243625           0.134220  \n",
       "shop_count                     0.875626        0.629895           0.376095  \n",
       "shop_pro                       0.717169        0.743497           0.414616  \n",
       "Coupon_diffcount               1.000000        0.520782           0.461961  \n",
       "Coupon_diffpro                 0.520782        1.000000           0.387775  \n",
       "same_Coupon_count              0.461961        0.387775           1.000000  \n",
       "\n",
       "[23 rows x 23 columns]"
      ]
     },
     "execution_count": 357,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr=oc_train.corr()\n",
    "corr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    data.drop(['Date_received'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "a=set(train['Merchant_id'].values)\n",
    "c=set(test['Merchant_id'].values)\n",
    "len(c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "len(a&c)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 训练模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "K=10\n",
    "from sklearn.metrics import roc_auc_score,auc\n",
    "from time import sleep\n",
    "import winsound\n",
    "from sklearn.model_selection import StratifiedKFold\n",
    "# kf = KFold(n_splits = K,random_state = 1,shuffle = True)\n",
    "kf=StratifiedKFold(n_splits = K,random_state = 90,shuffle = True)\n",
    "X=hhh.drop(['Date'],axis=1)\n",
    "y=hhh['Date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test=TEST.drop(['Date'],axis=1)\n",
    "test_y=TEST['Date']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 归一化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import MinMaxScaler, StandardScaler\n",
    "x_predict=test\n",
    "# 1. min max scaler\n",
    "min_max_scaler = MinMaxScaler()\n",
    "x_predict_min_max_scaled = min_max_scaler.fit_transform(x_predict)\n",
    "\n",
    "# 2. standard scaler\n",
    "std_scaler = StandardScaler()\n",
    "x_predict_std_scaled = std_scaler.fit_transform(x_predict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 1. min max scaler\n",
    "min_max_scaler = MinMaxScaler()\n",
    "# x_train_min_max_scaled = min_max_scaler.fit_transform(x_train)\n",
    "# x_test_min_max_scaled = min_max_scaler.transform(x_test)\n",
    "X_test_min_max_scaled=min_max_scaler.fit_transform(X)#整个数据集\n",
    "# 2. standard scaler\n",
    "std_scaler = StandardScaler()\n",
    "# x_train_std_scaled = std_scaler.fit_transform(x_train)\n",
    "# x_test_std_scaled = std_scaler.transform(x_test)\n",
    "X_test_std_scaled=std_scaler.fit_transform(X)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "accuracy: 0.914649710983\n",
      "Avg_auc for full training set: 0.849323214039\n"
     ]
    }
   ],
   "source": [
    "import xgboost as xgb\n",
    "#全部  0.5552 learning_rate=0.09\n",
    "model=xgb.XGBClassifier(n_estimators=200,\n",
    "                        max_depth=2,#6\n",
    "                        objective=\"binary:logistic\",\n",
    "                        learning_rate=0.09, \n",
    "                        subsample=.8,\n",
    "                        min_child_weight=6,\n",
    "                        colsample_bytree=.4,\n",
    "                        scale_pos_weight=1.6,\n",
    "                        gamma=9,\n",
    "                        seed=100,\n",
    "                        reg_alpha=8,\n",
    "                        reg_lambda=1.3)\n",
    "model.fit(X,y)\n",
    "pred=model.predict_proba(X)[:,1]\n",
    "print('accuracy:',model.score(X,y))\n",
    "pred_test=model.predict_proba(kkk)[:,1]\n",
    "print('Avg_auc for full training set:',Avg_auc(pred))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def Avg_auc(pred):\n",
    "    aucc=0\n",
    "    i=0\n",
    "    df=hhh.copy()\n",
    "    df['y_valid_pred']=pred\n",
    "    for j in list(set(df['Coupon_id'].values)):\n",
    "        df_1=df[df['Coupon_id']==j]\n",
    "        if len(np.unique(df_1['Date']))==1:\n",
    "            continue\n",
    "        aucc=aucc+roc_auc_score(df_1['Date'],df_1['y_valid_pred'])\n",
    "        i+=1\n",
    "    aucc=aucc/i                                                                      #len(set(df['Coupon_id'].values))\n",
    "    df.drop(['y_valid_pred'],axis=1,inplace=True)\n",
    "    return aucc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def Avg_testauc(pred):\n",
    "    aucc=0\n",
    "    i=0\n",
    "    df=TEST.copy()\n",
    "    df['y_valid_pred']=pred\n",
    "    for j in list(set(df['Coupon_id'].values)):\n",
    "        df_1=df[df['Coupon_id']==j]\n",
    "        if len(np.unique(df_1['Date']))==1:\n",
    "#             print(j)\n",
    "            continue\n",
    "        aucc=aucc+roc_auc_score(df_1['Date'],df_1['y_valid_pred'])\n",
    "        i+=1\n",
    "    aucc=aucc/i                                                     #len(set(df['Coupon_id'].values))\n",
    "    df.drop(['y_valid_pred'],axis=1,inplace=True)\n",
    "    return aucc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn.naive_bayes import GaussianNB\n",
    "model = GaussianNB()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn.neighbors import KNeighborsClassifier\n",
    "model=KNeighborsClassifier(n_neighbors=5)\n",
    "model.fit(X,y)\n",
    "pred=model.predict_proba(X)[:,1]\n",
    "print('Avg_auc for full training set:',Avg_auc(pred))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "accuracy: 0.999486705202\n"
     ]
    }
   ],
   "source": [
    "from sklearn.ensemble import RandomForestClassifier\n",
    "model=RandomForestClassifier(n_estimators=400)\n",
    "model.fit(X,y)\n",
    "pred=model.predict_proba(X)[:,1]\n",
    "pred_y=model.predict(X)\n",
    "print('accuracy:',model.score(X,y))\n",
    "rf_pred_test=model.predict_proba(kkk)[:,1]\n",
    "# print('Avg_auc for full training set:',Avg_auc(pred))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn.ensemble import AdaBoostClassifier#星期0.626\n",
    "model=AdaBoostClassifier(n_estimators=400,learning_rate=1,random_state=100)\n",
    "model.fit(X,y)\n",
    "pred=model.predict_proba(X)[:,1]\n",
    "print('accuracy:',model.score(X,y))\n",
    "adb_pred_test=model.predict_proba(kkk)[:,1]\n",
    "print('Avg_auc for full training set:',Avg_auc(pred))\n",
    "# print('Avg_auc for full test set:',Avg_testauc(pred_test))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "y_test_pred=0\n",
    "y_valid_pred=0*y\n",
    "fit_model=[]\n",
    "# XMIN=pd.DataFrame(X_test_std_scaled,columns=['Merchant_id', 'Coupon_id', 'Discount_rate', 'Distance', 'Date_received'])\n",
    "for i,(train_index,test_index) in enumerate(kf.split(X,y)):\n",
    "    y_train,y_valid=y.iloc[train_index],y.iloc[test_index]\n",
    "    x_train,x_valid=X.iloc[train_index],X.iloc[test_index]\n",
    "    print('\\nFlod:',i,end=':')\n",
    "    fitted_model=model.fit(x_train,y_train)\n",
    "    pred= fitted_model.predict_proba(x_valid)[:,1]\n",
    "    y_valid_pred.iloc[test_index]=pred\n",
    "    print(pd.DataFrame(pred).head(1))\n",
    "    \n",
    "    y_test_pred+=fitted_model.predict_proba(test)[:,1]\n",
    "    fit_model.append(fitted_model)\n",
    "    winsound.Beep(600,1000)\n",
    "    sleep(1)\n",
    "y_test_pred/=K\n",
    "print('Avg_auc for full training set:',Avg_auc(y_valid_pred))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "xgb=pred_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rf=rf_pred_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adb=adb_pred_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "h=pd.DataFrame()\n",
    "h['xgb']=xgb\n",
    "h['rf']=rf\n",
    "h['adb']=adb\n",
    "h['avg']=h.mean(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tt=pd.read_csv(r'D:\\Data\\TCForNewComer\\ccf_offline_stage1_test_revised.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sub=pd.DataFrame()\n",
    "sub['User_id']=tt['User_id']\n",
    "sub['Coupon_id']=tt['Coupon_id']\n",
    "sub['Date_received']=tt['Date_received']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sub['Probability']=xgb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sub.to_csv(r'D:\\Data\\TCForNewComer\\deal\\Same_userid\\xgbpred_result.csv',float_format='%.1f',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "y_train['a']=np.random.normal(len(y_train))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print('Avg_auc for full training set:',Avg_auc(y_valid_pred))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "from sklearn.metrics import roc_auc_score\n",
    "y_true = np.array([0, 1, 0, 1])\n",
    "y_scores = np.array([0.1, 0.4, 0.35, 0.8])\n",
    "y_true.astype(np.int64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
